﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class KassaObject
    {
        public const string sql = @"
SELECT 'Общая касса:' as Описание, '' as Сумма
UNION ALL
select '     Доход (всего)' as Описание, convert(nchar(20),sum(Summa)) as Сумма from Transactions where Type = 1
UNION ALL
select '     Расход (всего)' as Описание, convert(nchar(20),sum(Summa)) as Сумма from Transactions where Type = 2
UNION ALL
select '     Текущ.ост.' as Описание, convert(nchar(20),sum(a.b)) as Сумма from
((select sum(Summa) as b from Transactions where Type = 1)
union all
(select -sum(Summa) as b from Transactions where Type = 2)) as a
UNION ALL
SELECT 'Разбивка по проектам:' as Описание, '' as Сумма
UNION ALL
SELECT '     '+DictionaryProject.Name as Описание, convert(nchar(20),sum(a1.b1)) as Сумма FROM
((select Project, sum(Summa) as b1 from Transactions where Type = 1 group by Project)
union all
(select Project, -sum(Summa) as b1 from Transactions where Type = 2 group by Project)) as a1
LEFT JOIN DictionaryProject on a1.Project = DictionaryProject.ID
group by DictionaryProject.Name
UNION ALL
SELECT 'Разбивка по валютам:' as Описание,'' as Сумма
UNION ALL
SELECT '     '+DictionaryCurrency.ShortName as Описание, convert(nchar(20),sum(a2.b2)) as Сумма FROM
((select Currency, sum(Summa) as b2 from Transactions where Type = 1 group by Currency)
union all
(select Currency, -sum(Summa) as b2 from Transactions where Type = 2 group by Currency)) as a2
LEFT JOIN DictionaryCurrency on a2.Currency = DictionaryCurrency.ID
group by DictionaryCurrency.ShortName
";

        public string Описание { get; set; }
        public string Сумма { get; set; }
    }
}
